package com.dldata.devtools30.utils.dataexport;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.*;


public class JdbcDataAccess {

    private final static Logger log = LoggerFactory.getLogger(JdbcDataAccess.class);

    private DataSource dataSource;

    public synchronized static JdbcDataAccess getJdbcDataAcess(DataSource dataSource) {
        JdbcDataAccess jdbcDataAcess = new JdbcDataAccess();
        jdbcDataAcess.dataSource=dataSource;
        return jdbcDataAcess;
    }

    public void closeConnection(Connection connection, ResultSet resultSet, Statement preparedStatement) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                if (!connection.getAutoCommit())
                    connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Connection getConnection() throws SQLException {
        try {
            return dataSource.getConnection();
        } catch (SQLException ex) {
            log.error(ex.getMessage(), ex);
            throw ex;
        }
    }

    public List<Map<String,Object>> dbsearch(String sql, Object... parameters) {
        List<Map<String,Object>> relust = new LinkedList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; parameters!=null&& i < parameters.length; i++) {
                Object parameter = parameters[i];
                preparedStatement.setObject(i + 1, parameter);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            while (resultSet.next()) {
                TreeMap<String, Object> stringObjectHashMap = new TreeMap<String, Object>(String.CASE_INSENSITIVE_ORDER);
                for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                    String columnName = resultSetMetaData.getColumnLabel(i);
                    switch (resultSetMetaData.getColumnType(i)) {
                        case Types.DATE: {
                            Date anInt = resultSet.getDate(i);
                            String dateString = anInt == null ? "NULL" : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(anInt);
                            stringObjectHashMap.put(columnName, dateString);
                            break;
                        }
                        case Types.TIME: {
                            Time time = resultSet.getTime(i);
                            String dateString = time == null ?  "NULL" :new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(time);
                            stringObjectHashMap.put(columnName, dateString);
                            break;
                        }
                        case Types.TIMESTAMP: {
                            Object timestamp1 = resultSet.getObject(i);
                            Timestamp timestamp =timestamp1==null? null:resultSet.getTimestamp(i);
                            String dateString = timestamp == null ? "NULL" : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timestamp);
                            stringObjectHashMap.put(columnName, dateString);
                            break;
                        }
                        case Types.TINYINT:
                        case Types.SMALLINT:
                        case Types.INTEGER: {
                            ;
                            Long anInt = resultSet.getLong(i);
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                        case Types.FLOAT: {
                            ;
                            Float anInt = resultSet.getFloat(i);
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                        case Types.DOUBLE: {
                            ;
                            Double anInt = resultSet.getDouble(i);
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                        case Types.CLOB: {
                            ;
                            try {
                                Clob colb = (Clob) resultSet.getObject(i);
                                stringObjectHashMap.put(columnName, colb == null ? "" : colb.getSubString(1, (int) colb.length()));
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        case Types.BLOB: {
                            try {
                                Blob blob = (Blob) resultSet.getObject(i);
//                            stringObjectHashMap.put(columnName,new String(blob.getBytes(0, (int) blob.length()),"UTF-8"));
                                //@TODO 后面考虑转二进制可显示串
                                stringObjectHashMap.put(columnName, blob == null ? "" : blob.toString());
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        default: {
                            String anInt = resultSet.getString(i);
                            anInt = anInt == null ? "" : anInt;
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                    }
                }
                relust.add(stringObjectHashMap);
            }


        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
        } finally {
            closeConnection(connection, null, null);
        }
        return relust;
    }

    public boolean tableFiledIsNumber(String filedName, String tableName) {
        Connection connection = null;
        try {
            connection = getConnection();
            String sql = "select " + filedName + " from " + tableName + " where 1=2 ";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            switch (resultSetMetaData.getColumnType(1)) {
                case Types.NUMERIC:
                case Types.DECIMAL:
                case Types.DOUBLE:
                case Types.FLOAT:
                case Types.TINYINT:
                case Types.SMALLINT:
                case Types.INTEGER: {
                    return true;
                }
                case Types.BLOB:
                case Types.CLOB:
                case Types.TIMESTAMP:
                case Types.TIME:
                case Types.DATE: {
                    return false;
                }
                default: {
                    return false;
                }
            }
        } catch (Exception ex) {
            log.error(ex.getMessage()+" "+tableName+"  "+filedName, ex);
            return false;
        } finally {
            closeConnection(connection, null, null);
        }
    }
}
